/***********************************************************************************************************************************
 *    Name: FileGrowth.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 

<= 64MB: 4 VLFs
< 64MB and <= 1GB: 8 VLFs
< 1GB: 16 VLFs

	Size (MB)	Pages	VLFs	VLF Size
	   8MB		  1024	 4		  256 (  4MB)
	  64MB		  8192	 4		 2048 ( 16MB)
	 128MB		 16384	 8		 2048 ( 16MB)
	 256MB		 32768	 8		 4096 ( 32MB)
	 512MB		 65536	 8		 8192 ( 64MB)
	1024MB		131072	 8		16384 (128MB)
	2048MB		262144	16		16384 (128MB)
	4096MB		524288	16		32768 (256MB)

**/
USE master;
GO
CREATE TABLE #dbfiles (
	[db_name]			SYSNAME	NOT NULL,
	[file_id]			INT		NOT NULL,
	[type]				TINYINT	NOT NULL,
	[name]				SYSNAME	NOT NULL,
	[size]				INT		NOT NULL,	-- #8KB pages
	[growth]			INT		NOT NULL,	-- #8KB pages
	[is_percent_growth]	BIT		NOT NULL,
	[max_size]			INT		NOT NULL);	-- #8KB pages

DECLARE
  @template	NVARCHAR(MAX)= N'INSERT INTO #dbfiles SELECT N''?'', file_id, type, name, size, growth, is_percent_growth, max_size FROM [?].sys.database_files;',
  @tsql		NVARCHAR(MAX)= N';',
  @db_name	SYSNAME,
  @file_id	INT,
  @type		TINYINT,
  @name		SYSNAME,
  @size		INT,
  @growth	INT,
  @max_size	INT;
SET NOCOUNT ON;
SELECT @tsql+= REPLACE(@template, N'?', name) FROM sys.databases d WHERE 4 < database_id AND [state] = 0 AND is_read_only = 0;
EXECUTE (@tsql);
SET NOCOUNT OFF;

DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT [db_name], [name] FROM #dbfiles WHERE [type] = 1 AND is_percent_growth = 1 ORDER BY db_name;
OPEN list;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM list INTO @db_name, @name;
  IF @@FETCH_STATUS <> 0 BREAK;
  SELECT @size= MAX([size]) FROM #dbfiles WHERE [db_name]= @db_name AND [type] = 0;
  SELECT @size= @size/10, @growth= 8;
  IF   8192 <= @size SET @growth=   64;
  IF  16384 <= @size SET @growth=  128;
  IF  32768 <= @size SET @growth=  256;
  IF  65536 <= @size SET @growth=  512;
  IF 131072 <= @size SET @growth= 1024;
  IF 262144 <= @size SET @growth= 2048;
  IF 524288 <= @size SET @growth= 4096;

  PRINT N'ALTER DATABASE [' + @db_name + '] MODIFY FILE (NAME= N''' + @name + ''', FILEGROWTH= ' + CAST(@growth AS NVARCHAR(5)) + 'MB, MAXSIZE= UNLIMITED);';

END
CLOSE list;
DEALLOCATE list;
GO
